﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SQLServerImporter
{
    static class Program
    {
        static public DataTable ConvertToDataTable(SqlDataReader dataReader)
        {
            DataTable dt = new DataTable();
            DataTable schemaTable = dataReader.GetSchemaTable();
            try
            {
                //动态构建表，添加列
                foreach (DataRow dr in schemaTable.Rows)
                {
                    DataColumn dc = new DataColumn();
                    //设置列的数据类型
                    dc.DataType = dr[0].GetType();
                    //设置列的名称
                    dc.ColumnName = dr[0].ToString();
                    //将该列添加进构造的表中
                    dt.Columns.Add(dc);
                }
                //读取数据添加进表中
                while (dataReader.Read())
                {
                    DataRow row = dt.NewRow();
                    //填充一行数据
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        row[i] = dataReader[i].ToString();

                    }
                    dt.Rows.Add(row);
                    row = null;
                }
                dataReader.Close();
                schemaTable = null;
                return dt;
            }
            catch (Exception ex)
            {

                //抛出异常
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            //Application.EnableVisualStyles();
            //Application.SetCompatibleTextRenderingDefault(false);
            //Application.Run(new Form1());



            using (var conn = new SqlConnection("Data Source=192.168.1.123,1433;Initial Catalog=nopCommerce;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=qwe123123@"))
            {
                conn.Open();
                using (var command = new SqlCommand("TRUNCATE TABLE ChinaArea", conn))
                {
                    command.ExecuteNonQuery();
                }
                DataTable dt;
                using (var command = new SqlCommand("select * from ChinaArea", conn))
                {
                    dt = ConvertToDataTable(command.ExecuteReader());
                }



                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    //每一批次中的行数
                    bulkCopy.BatchSize = 100000;
                    //超时之前操作完成所允许的秒数
                    bulkCopy.BulkCopyTimeout = 1800;

                    //将DataTable表名作为待导入库中的目标表名
                    bulkCopy.DestinationTableName = "ChinaArea";

                    //将数据集合和目标服务器库表中的字段对应 
                    for (int i = 1; i < dt.Columns.Count; i++)
                    {
                        //列映射定义数据源中的列和目标表中的列之间的关系
                        bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }

                    using (var sr = File.OpenText("area_code_2019.csv"))
                    {
                        dt.Clear();
                        int count = 0;
                        while (true)
                        {

                            if (count == 100000 || sr.Peek() < 1)
                            {
                                count = 0;
                                //将DataTable数据上传到数据表中
                                bulkCopy.WriteToServer(dt);
                                dt.Clear();
                                if (sr.Peek() < 1)
                                {
                                    Console.WriteLine("写入完成");
                                    return;
                                }
                            }
                            var line = sr.ReadLine();
                            //code,name,level,pcode
                            var datas = line.Trim().Split(',');
                            DataRow dr = dt.NewRow();
                            dr["Code"] = long.Parse(datas[0]);
                            dr["Name"] = datas[1];
                            dr["Level"] = byte.Parse(datas[2]);
                            dr["PostCode"] = long.Parse(datas[3]);
                            dt.Rows.Add(dr);
                            count++;
                        }
                    }

                }

            }

          

        }
    }
}
